package srzy.driver

import srzy.dao.DBUtil
import srzy.common.Logging
import spray.json._
import DefaultJsonProtocol._
import srzy.common.Common
import net.sf.jsqlparser.parser.CCJSqlParserManager
import java.io.StringReader
import net.sf.jsqlparser.statement.update.Update
import net.sf.jsqlparser.schema.Column
import net.sf.jsqlparser.expression.operators.relational.EqualsTo
import net.sf.jsqlparser.expression.Expression
import net.sf.jsqlparser.expression.BinaryExpression
import net.sf.jsqlparser.expression.StringValue
import java.io.File
import srzy.diameter._
import scala.collection.mutable.ListBuffer

object Start8 extends Logging {

  def main(args: Array[String]): Unit = {

    deal1
    deal2
    deal3
    deal4
    deal5
    deal6
    //    deal7
  }

  def deal1 = {
    val DBOracle = DBUtil("CaseInfoSource")
    val l = DBOracle.getCaseInfo2
    val l2: List[(String, String)] = l.toList.map(x => (x._1, x._2._6))
    //    logger.debug("l2:" + l2.mkString("\n"))
    val str = l2.toJson.prettyPrint
    Common.writeToFile("1.txt", str)
  }

  def deal2 = {
    val l3 = Common.readFromFile2("1.txt").asJson.convertTo[List[(String, String)]]
    val l4 = l3.filter(_._2 != "")
    //              logger.debug("l4:\n" + l4.map(x => x._1 + "\n" + x._2).mkString("\n--------------\n"))
    val r1 = """sqlplus[ ]+hn_au/hn_au@ocs[ ]+<<!\n([\p{ASCII}]*?)\n!""".r

    val l5 = l4.slice(0, 10000).map(x => {
      val sqls = r1.findAllMatchIn(x._2).toList.map(_.group(1)).flatMap(parser(_))
      //      logger.debug("\n" + x._1 + "\n" + x._2 + "\n--------------\n")
      (x._1, sqls)
    }).filter(_._2.length > 0)
    Common.writeToFile("oracle.txt", l5.toJson.prettyPrint)
    //    println(l5.map(x => x._1 + "\n" + x._2).mkString("\n"))

  }

  def deal3 = {
    val l3 = Common.readFromFile2("1.txt").asJson.convertTo[List[(String, String)]]
    val l4 = l3.filter(_._2 != "")
    //              logger.debug("l4:\n" + l4.map(x => x._1 + "\n" + x._2).mkString("\n--------------\n"))
    val r1 = """sqlplus[ ]+hn_au/hn_au@ocs[ ]+<<!\n([\p{ASCII}]*)\n!""".r

    val l5 = l4.slice(0, 10000).map(x => {
      (x._1, r1.replaceAllIn(x._2, ""))
    })
    Common.writeToFile("2.txt", l5.toJson.prettyPrint)
    val txt = l5.map(x => x._1 + "\n" + x._2).mkString("\n")
    Common.writeToFile("2.log", txt)
    //    println(txt)
  }

  //  def deal4 = {
  //    val l3 = Common.readFromFile2("2.txt").asJson.convertTo[List[(String, String)]]
  //    val l4 = l3.filter(_._2 != "")
  //    //              logger.debug("l4:\n" + l4.map(x => x._1 + "\n" + x._2).mkString("\n--------------\n"))
  //    val r = """isql[ ]+([\p{ASCII}]+?)[ ]+<<!\n([\p{ASCII}]*?)\n!""".r
  //    val r1 = """isql[ ]+-s[ ]+172.168.1.245[ ]+-port[ ]+20307[ ]+-u[ ]+hn_sv1[ ]+-p[ ]+hn_sv1[ ]+-NLS_USE[ ]+US7ASCII[ ]+<<!\n([\p{ASCII}]*?)\n!""".r
  //    val r2 = """isql[ ]+-s[ ]+172.168.1.231[ ]+-u[ ]+hn_au[ ]+-p[ ]+hn_au[ ]+-port[ ]+20332[ ]+-NLS_USE[ ]+US7ASCII[ ]+<<!\n([\p{ASCII}]*?)\n!""".r
  //
  //    val l5 = l4.slice(0, 10000).map(x => {
  //      val sqls1 = r1.findAllMatchIn(x._2).toList.map(_.group(1)).flatMap(parser(_))
  //      val sqls2 = r2.findAllMatchIn(x._2).toList.map(_.group(1)).flatMap(parser(_))
  //      //      (x._1, sqls)
  //      //      (x._1, r2.replaceAllIn(r1.replaceAllIn(x._2, ""), ""))
  //      (x._1, sqls1, sqls2)
  //
  //    }).filter(x => x._2.length + x._3.length != 0)
  //    Common.writeToFile("altibase.txt", l5.toJson.prettyPrint)
  //    //    println(l5.map(x => x._1 + "\n" + x._2).mkString("\n"))
  //    println(l5.map(x => x._1 + "\nRBAltibase:\n" + x._2.mkString("\n")
  //      + "\nSMAltibase:\n" + x._3.mkString("\n")).mkString("\n-----------\n"))
  //
  //  }

  def deal4 = {
    val l3 = Common.readFromFile2("2.txt").asJson.convertTo[List[(String, String)]]
    val l4 = l3.filter(_._2 != "")
    //              logger.debug("l4:\n" + l4.map(x => x._1 + "\n" + x._2).mkString("\n--------------\n"))
    val r = """isql[ ]+([\p{ASCII}]+?)[ ]+<<!\n([\p{ASCII}]*?)\n!""".r

    val rbAltibaseInfo = srzy.common.AltibaseInfo("172.168.1.245", "20307", "hn_sv1", "hn_sv1")
    val smAltibaseInfo = srzy.common.AltibaseInfo("172.168.1.231", "20332", "hn_au", "hn_au")

    val l5 = l4.slice(0, 10000).map(x => {
      val res = r.findAllMatchIn(x._2).toList.flatMap((e => {
        val connInfo = e.group(1)
        val altibaseInfo = srzy.common.AltibaseConnStrParser.parseInfo(connInfo)
        val sqlText = e.group(2)
        parser(sqlText).map(q => (altibaseInfo, q))
      }))
      val rbAltibaseInfos = ListBuffer.empty[String]
      val smAltibaseInfos = ListBuffer.empty[String]
      res.foreach(w => {
        if (w._1 == rbAltibaseInfo)
          rbAltibaseInfos.append(w._2)
        else if (w._1 == smAltibaseInfo)
          smAltibaseInfos.append(w._2)
        else
          logger.error("AltibaseInfo:" + w._1 + " , sql:" + w._2)
      })
      (x._1, rbAltibaseInfos.toList, smAltibaseInfos.toList)

    }).filter(x => x._2.length + x._3.length != 0)
    Common.writeToFile("altibase.txt", l5.toJson.prettyPrint)
    //    println(l5.map(x => x._1 + "\n" + x._2).mkString("\n"))
    //    println(l5.map(x => x._1 + "\nRBAltibase:\n" + x._2.mkString("\n")
    //      + "\nSMAltibase:\n" + x._3.mkString("\n")).mkString("\n-----------\n"))

  }

  //  def deal5 = {
  //    val l3 = Common.readFromFile2("2.txt").asJson.convertTo[List[(String, String)]]
  //    val l4 = l3.filter(_._2 != "")
  //    val r1 = """isql[ ]+-s[ ]+172.168.1.245[ ]+-port[ ]+20307[ ]+-u[ ]+hn_sv1[ ]+-p[ ]+hn_sv1[ ]+-NLS_USE[ ]+US7ASCII[ ]+<<!\n([\p{ASCII}]*?)\n!""".r
  //    val r2 = """isql[ ]+-s[ ]+172.168.1.231[ ]+-u[ ]+hn_au[ ]+-p[ ]+hn_au[ ]+-port[ ]+20332[ ]+-NLS_USE[ ]+US7ASCII[ ]+<<!\n([\p{ASCII}]*?)\n!""".r
  //
  //    val l5 = l4.slice(0, 10000).map(x => {
  //      (x._1, r2.replaceAllIn(r1.replaceAllIn(x._2, ""), ""))
  //    })
  //
  //    Common.writeToFile("3.txt", l5.toJson.prettyPrint)
  //
  //    val txt = l5.map(x => x._1 + "\n" + x._2).mkString("\n")
  //    Common.writeToFile("3.log", txt)
  //    println(txt)
  //  }

  def deal5 = {
    val l3 = Common.readFromFile2("2.txt").asJson.convertTo[List[(String, String)]]
    val l4 = l3.filter(_._2 != "")
    val r = """isql[ ]+([\p{ASCII}]+?)[ ]+<<!\n([\p{ASCII}]*?)\n!""".r

    val rbAltibaseInfo = srzy.common.AltibaseInfo("172.168.1.245", "20307", "hn_sv1", "hn_sv1")
    val smAltibaseInfo = srzy.common.AltibaseInfo("172.168.1.231", "20332", "hn_au", "hn_au")

    val l5 = l4.slice(0, 10000).map(x => {
      (x._1, r.replaceAllIn(x._2, e => {
        val connInfo = e.group(1)
        val altibaseInfo = srzy.common.AltibaseConnStrParser.parseInfo(connInfo)
        if (altibaseInfo == rbAltibaseInfo || altibaseInfo == smAltibaseInfo)
          ""
        else
          e.group(0)
      }))
    })

    Common.writeToFile("3.txt", l5.toJson.prettyPrint)

    val txt = l5.map(x => x._1 + "\n" + x._2).mkString("\n")
    Common.writeToFile("3.log", txt)
    //    println(txt)

  }

  def deal6 = {
    val oracle = Common.readFromFile2("oracle.txt").asJson.convertTo[List[(String, List[String])]]
    val altibase = Common.readFromFile2("altibase.txt").asJson.convertTo[List[(String, List[String], List[String])]]

    val rbAltibase = altibase.map(x => (x._1, x._2)).filter(_._2.length != 0)
    val smAltibase = altibase.map(x => (x._1, x._3)).filter(_._2.length != 0)

    val smOracleMap = oracle.toMap
    val rbAltibaseMap = rbAltibase.toMap
    val smAltibaseMap = smAltibase.toMap

    val l = new File("case")
      .listFiles()
      .filter(_.isDirectory)
      .flatMap(x => x.listFiles())
      .filter(_.getName().takeRight(3) == "xml")
      .map(_.getPath())
    l.foreach(x => {
      logger.debug("deal : " + x)
      val caseName = new File(x).getName()
      val text = addDBInit(x, smOracleMap.get(caseName),
        rbAltibaseMap.get(caseName),
        smAltibaseMap.get(caseName))
      srzy.common.writeToFile("new" + x, text)
    })
  }

  def deal7 = {
    //    val l = new File("case")
    //      .listFiles()
    //      .filter(_.isDirectory)
    //      .flatMap(x => x.listFiles())
    //      .filter(_.getName().takeRight(3) == "xml")
    //      .map(_.getPath())
    val l = ListBuffer.empty[File]
    val root = new File("case")
    def walk(file: File) {
      if (file.isFile()) l.append(file) else file.listFiles().foreach(walk)
    }
    walk(root)
    val ll = l.filter(_.getName().takeRight(3) == "xml")
    ll.slice(0, 10000).foreach(x => {
      val path = x.getPath()
      logger.debug("deal : " + path)
      val caseName = x.getName()
      val text = addDBInit(path, None,
        None,
        None)
      val r = """<avp name="Session-Id" value="[\u0000-\uffff]*?"></avp>""".r
      val text0 = r.replaceAllIn(text, """<avp name="Session-Id" value="sm.auto.test;""" +
        path.slice(0, path.lastIndexOf(".xml")).split(File.separatorChar).toList.drop(1).mkString(";") + """"></avp>""")
      srzy.common.writeToFile("new" + path, text0)
    })
  }

  def addDBInit(filePath: String,
    oracle: Option[List[String]],
    rbAltibase: Option[List[String]],
    smAltibase: Option[List[String]]) = {

    val text0 = srzy.common.readFromFile(filePath, "GBK").drop(1).mkString("\n")
    //    val r = """[\u4e00-\u9fa5]+""".r
    //    val text1 = r.replaceAllIn(text0, "")
    val r1 = """<!--[\u0000-\uffff]*?-->""".r
    val text = r1.replaceAllIn(text0, "")
    val traffic = try {
      (scala.xml.XML.loadString(text) \\ "traffic").head
    } catch {
      case e: Exception => println("\n" + text); throw e
    }

    var blocks = scala.xml.NodeSeq.Empty

    oracle match {
      case Some(v) =>
        val node = <db name="SMOracleSource">{ v.map(e => <sql>{ e }</sql>) }</db>
        blocks = blocks ++ node
      case None =>
    }

    rbAltibase match {
      case Some(v) =>
        val node = <db name="RBInMemorySource">{ v.map(e => <sql>{ e }</sql>) }</db>
        blocks = blocks ++ node
      case None =>
    }

    smAltibase match {
      case Some(v) =>
        val node = <db name="SMInMemorySource">{ v.map(e => <sql>{ e }</sql>) }</db>
        blocks = blocks ++ node
      case None =>
    }

    blocks = traffic.nonEmptyChildren.toList.foldLeft(blocks)((l, x) => {
      x.label match {
        case "send" =>
          val send = (x \ "command").head
          l ++ <send channel="channel-1"> { send.toBytes.toDCCNode }</send>
        case "receive" =>
          l ++ <receive channel="channel-1">
                 <command name="CCA"></command>
               </receive>
        case "RBSleep" =>
          l ++ <RBSleep></RBSleep>
        case "RBSleepAndDiscard" =>
          l ++ <RBSleepAndDiscard></RBSleepAndDiscard>
        case "wait-ms" | "db" | "cache" | "CleanCache" | "shell" | "refreshSM" | "DCCTimeoutTime" =>
          l ++ x
        case _ =>
          val r1 = """[\p{Space}]+""".r
          if (r1.pattern.matcher(x.toString).matches() == false) {
            logger.error("filePath:" + filePath + "\nx:" + x)
          }
          l ++ x
      }
    })

    val scenario = <scenario>
                     <init>
                       <send channel="channel-1">
                         <command name="CER">
                           <avp name="Origin-Host" value="sm-all-test"></avp>
                           <avp name="Origin-Realm" value="chinaunicom.com"></avp>
                           <avp name="Host-IP-Address" value="0x000100000000"></avp>
                           <avp name="Vendor-Id" value="11"></avp>
                           <avp name="Product-Name" value="HP Cx Interface"></avp>
                           <avp name="Origin-State-Id" value="62"></avp>
                           <avp name="Supported-Vendor-Id" value="10415"></avp>
                           <avp name="Auth-Application-Id" value="4"></avp>
                           <avp name="Acct-Application-Id" value="0"></avp>
                           <avp name="Firmware-Revision" value="1"></avp>
                         </command>
                       </send>
                       <receive channel="channel-1">
                         <command name="CEA"></command>
                       </receive>
                     </init>
                     <traffic>
                       { blocks }
                     </traffic>
                   </scenario>

    val llll = new srzy.common.PrettyPrinter(800, 2)

    val txt = """<?xml version="1.0" encoding="iso-8859-1"?>""" + "\n" + llll.format(scenario)

    //    logger.debug("txt:" + txt)
    txt
  }

  def parser(l: String) = {
    //    val l = """update sm_system_parameter set para_value=0 where para_key='REGION_SWITCH';
    //commit;
    //update sm_params_refresh_control a set a.refresh_flag= 19
    //where  a.sm_host='ocssm134';
    //commit;
    //exit"""
    //    logger.debug("l:" + l)
    val ll = l.split("\n").toList.filter(!_.contains("commit"))
      .filter(!_.contains("exit")).mkString("\n")
    //    logger.debug("\n" + ll)
    val parserManager = new CCJSqlParserManager()
    ll.split(";").toList.filter(!isRefreshControl(_))
      .map(_.replaceAll("\n", ""))
      //      .map(_.replaceAll("[ ]+", " "))
      .map(x => {
        //                logger.debug("sql:" + x)
        parserManager.parse(new StringReader(x)).toString

      })
    //    ll.split(";").toList.filter(!isRefreshControl(_)).foreach { x =>
    //      val c = parserManager.parse(new StringReader(x))
    //      logger.debug("c:" + c)
    //      c match {
    //        case s: Update =>
    //          logger.debug("" + s.getTable().getName())
    //
    //          val cols = s.getColumns().toArray().toList.asInstanceOf[List[Column]].map(_.getColumnName())
    //          logger.debug("" + cols)
    //          val exps = s.getExpressions().toArray().toList.asInstanceOf[List[Expression]]
    //          logger.debug("" + exps)
    //
    //          val where = s.getWhere()
    //          where match {
    //            case b: BinaryExpression =>
    //              b.getLeftExpression() match {
    //                case c: Column => logger.debug(c.getColumnName())
    //                case _ => logger.error("where clouse LeftExpression not match Column")
    //              }
    //              logger.debug("=")
    //              b.getRightExpression() match {
    //                case str: StringValue => logger.debug(str.getValue())
    //                case _ => logger.error("where clouse RightExpression not match StringValue")
    //              }
    //            case _ => logger.error("where clouse not match BinaryExpression")
    //
    //          }
    //        case _ => logger.error("not match update")
    //      }
    //    }

  }

  def isRefreshControl(sql: String): Boolean = {
    val parserManager = new CCJSqlParserManager()
    val c = parserManager.parse(new StringReader(sql))
    c match {
      case s: Update =>
        if (s.getTable().getName().toLowerCase() == "sm_params_refresh_control")
          true
        else
          false
      case _ => false
    }
  }

}